# 版本说明:  2024.12.10 新建
# 制作人员:  dzy
# 功能说明：用于连接数据库
# 备注说明：
# 环境要求： python3/ windows32or64
# 设备要求：


# ---内置库---
from sqlite3 import OperationalError
import os
import sys
import time

# ---第三方库---
from colorama import init, Fore, Back, Style
# import pymysql
import sqlite3

# ---自研库---
from lib.mytools import getNowTime
from lib.mytools import basicClass


class Sqlite_Connector(basicClass):
    def __init__(self):
        super().__init__()
        self.instanceName = 'Sqlite_Connector'
        self._ready_flag = None  # 数据库连接状态
        # 建立连接

    def connect(self, db_path):
        try:
            # 主代码块
            actionName = '连接'
            actionValue = '数据库'
            # 连接到数据库（如果数据库文件不存在，会自动创建）
            self.conn = sqlite3.connect(db_path)
            # 设置 row_factory 为 sqlite3.Row，这样查询结果就会返回字典类型
            self.conn.row_factory = sqlite3.Row
        except Exception as e:
            # 异常时，执行该块
            msg_err = f'{actionName} {actionValue}发生错误 {e}'
            self.classPrint(Fore.RED + msg_err)
            self._ready_flag = False
            return False
        else:
            # 主代码块执行完，执行该块
            msg = actionName + ' ' + actionValue + '执行正常'
            self.classPrint(Fore.GREEN + msg)
            self._ready_flag = True
            return True

    def create_db(self):  # TODO 未测试
        try:
            # 主代码块
            actionName = '创建'
            actionValue = '一个表'
            cur = self.conn.cursor()
            sql = """CREATE TABLE gas_price (
                        id integer primary key autoincrement,
                        coin_name varchar(15) not null,
                        low integer,
                        avg integer,
                        high integer,
                        c_time integer
                    );"""
            cur.execute(sql)
            self.classPrint("create table success")
        except Exception as e:
            msg_err = f'{actionName} {actionValue}发生错误 {e}'
            print(Fore.RED + msg_err)
            return False
        else:
            msg = actionName + ' ' + actionValue + '执行正常'
            self.classPrint(Fore.GREEN + msg)
            cur.close()
            return True

    def alter_table(self):  # TODO 未完成
        """ 未完成
        """
        cur = self.conn.cursor()
        cur.execute("create table test_tmp as select * from gas_price;")
        cur.execute("drop table gas_price;")
        cur.execute("""create table gas_price (
                        id integer primary key autoincrement,
                        coin_name varchar(15) not null,
                        low integer not null,
                        avg integer not null,
                        high integer not null,
                        c_time integer not null
                    );""")

        cur.execute("insert into gas_price select * from test_tmp;")
        cur.execute("drop table test_tmp;")
        self.conn.commit()

    def is_exist_table(self, table_name):  # TODO  未测试
        cursor = self.conn.cursor()
        # 查询是否存在名为 'level_0' 的表
        cursor.execute(
            F"SELECT name FROM sqlite_master WHERE type='table' AND name='{table_name}';")
        table = cursor.fetchone()
        if table:
            print("表 'level_0' 存在")
        else:
            print("表 'level_0' 不存在")
        # 关闭连接
        cursor.close()

    def execute_sql(self, sql_string):
        try:
            # 创建游标对象
            cursor = self.conn.cursor()
            # 执行 SQL 查询
            cursor.execute(sql_string)
            self.conn.commit()
            # 获取查询结果
            results = cursor.fetchall()
            return results
        except Exception as e:
            msg = f'发生错误! {e}'
            self.classPrint(Fore.RED + msg)
            return False
        finally:
            # 关闭连接
            cursor.close()

    def select_10_item(self, tablename):
        try:
            # 创建游标对象
            cursor = self.conn.cursor()
            # 执行 SQL 查询
            cursor.execute(f"SELECT * FROM {tablename} LIMIT 10;")
            # 获取查询结果
            results = cursor.fetchall()
            for row in results:
                print(row)
        finally:
            # 关闭连接
            cursor.close()
            self.conn.close()

    def select_items(self, tablename):
        try:
            # 创建游标对象
            cursor = self.conn.cursor()
            # 执行 SQL 查询
            cursor.execute(f"SELECT * FROM {tablename} ;")
            # 获取查询结果
            results = cursor.fetchall()
            for row in results:
                print(row.keys())
                break
            return results
        except Exception as e:
            msg = f'发生错误! {e}'
            self.classPrint(Fore.RED + msg)
            return False
        finally:
            # 关闭连接
            cursor.close()

    def __del__(self):
        # 关闭游标和连接
        self.conn.close()


if __name__ == '__main__':
    渤海QMS_db = Mysql_connector()
    渤海QMS_db.select_10_item('mm_unit')
    pass
